<?php
if (!defined("BASEPATH")) exit("No direct script access allowed");

class Search extends MY_Model {

	function __construct() {
		parent::__construct();
	}

	function getSearchList($searchtext = NULL, $start = NULL, $limit = NULL) {
		$qs =	"

					select
						'CON' as type, con_id as id, con_url_name as url, con_name as name, coalesce(e2c_int, e2c_text) as description
						, '' as con_url_name, '' as cou_url_name, '' as city_url_name, '' as ins_url_name, '' as cou_name, '' as city_name, '' as ins_name
					from
						".$this->db->dbprefix."continent
						join ".$this->db->dbprefix."ent2cat on e2c_entity = 'CON' and e2c_ent_id = con_id and e2c_del = 0 and coalesce(e2c_int, e2c_text) is not null and e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryCONDescriptionId"))."'
					where
						con_del = 0 and (con_name like '%".$this->db->escape_str($searchtext)."%' or e2c_int like '%".$this->db->escape_str($searchtext)."%' or match (e2c_text) against ('".$this->db->escape_str($searchtext)."'))

					union all

					select
						'COU' as type, cou_id as id, concat(con_url_name,'/',cou_url_name) as url, cou_name as name, coalesce(e2c_int, e2c_text) as description
						, '', '', '', '', '', '', ''
					from
						".$this->db->dbprefix."country
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
						join ".$this->db->dbprefix."ent2cat on e2c_entity = 'COU' and e2c_ent_id = cou_id and e2c_del = 0 and coalesce(e2c_int, e2c_text) is not null and e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryCOUDescriptionId"))."'
					where
						cou_del = 0 and cou_presenter = 'I' and (cou_name like '%".$this->db->escape_str($searchtext)."%' or e2c_int like '%".$this->db->escape_str($searchtext)."%' or match (e2c_text) against ('".$this->db->escape_str($searchtext)."'))

					union all

					select
						'CITY' as type, city_id as id, concat(con_url_name,'/',cou_url_name,'/',city_url_name) as url, city_name as name, coalesce(e2c_int, e2c_text) as description
						, con_url_name, cou_url_name, city_url_name, '', cou_name, city_name, ''
					from
						".$this->db->dbprefix."city
						join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
						join ".$this->db->dbprefix."ent2cat on e2c_entity = 'CITY' and e2c_ent_id = city_id and e2c_del = 0 and coalesce(e2c_int, e2c_text) is not null and e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryCITYDescriptionId"))."'
					where
						city_del = 0 and city_presenter = 'I' and (city_name like '%".$this->db->escape_str($searchtext)."%' or e2c_int like '%".$this->db->escape_str($searchtext)."%' or match (e2c_text) against ('".$this->db->escape_str($searchtext)."'))

					union all

					select
						'INS' as type, ins_id as id, concat('institutes/',ins_url_name) as url, ins_name as name, coalesce(e2c_int, e2c_text) as description
						, con_url_name, cou_url_name, city_url_name, ins_url_name, cou_name, city_name, ins_name
					from
						".$this->db->dbprefix."institute
						join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
						join ".$this->db->dbprefix."ent2cat on e2c_entity = 'INS' and e2c_ent_id = ins_id and e2c_del = 0 and coalesce(e2c_int, e2c_text) is not null and e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryINSDescriptionId"))."'
					where
						ins_del = 0 and ins_presenter = 'I' and (ins_name like '%".$this->db->escape_str($searchtext)."%' or e2c_int like '%".$this->db->escape_str($searchtext)."%' or match (e2c_text) against ('".$this->db->escape_str($searchtext)."'))

					union all

					select
						'MAJ' as type, maj_id as id, concat('institutes/',ins_url_name,'/',maj_url_name) as url, maj_name as name, coalesce(e2c_int, e2c_text) as description
						, con_url_name, cou_url_name, city_url_name, ins_url_name, cou_name, city_name, ins_name
					from
						".$this->db->dbprefix."major
						join ".$this->db->dbprefix."subject on sub_id = maj_sub_id and sub_del = 0
						join ".$this->db->dbprefix."institute on ins_id = maj_ins_id and ins_del = 0
						join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
						join ".$this->db->dbprefix."ent2cat on e2c_entity = 'MAJ' and e2c_ent_id = maj_id and e2c_del = 0 and coalesce(e2c_int, e2c_text) is not null and e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryMAJDescriptionId"))."'
					where
						maj_del = 0 and maj_presenter = 'I' and (maj_name like '%".$this->db->escape_str($searchtext)."%' or e2c_int like '%".$this->db->escape_str($searchtext)."%' or match (e2c_text) against ('".$this->db->escape_str($searchtext)."'))

					union all

					select
						'ART' as type, art_id as id, art_url_name as url, art_name as name, coalesce(art_lead, art_text) as description
						, '', '', '', '', '', '', ''
					from
						".$this->db->dbprefix."article
					where
						art_del = 0 and (art_name like '%".$this->db->escape_str($searchtext)."%' or art_lead like '%".$this->db->escape_str($searchtext)."%' or art_text like '%".$this->db->escape_str($searchtext)."%')

					order by
						name
					".((is_numeric($start))?(" limit ".$start.",".$limit):(""))."
					";
		//$this->db->cache_off();
		$result = $this->db->query($qs);
		//Common::pr($this->db->last_query());
		$qs = "SELECT '".md5($qs)."' as prevsql, FOUND_ROWS() as cnt";
		$resultCnt = $this->db->query($qs)->result();
		//Common::pr($resultCnt);

		$getSearchList = array();
		if($result->num_rows()) {
			$getSearchList["cnt"] = $resultCnt[0]->cnt;
			foreach ($result->result() as $row) {
				$getSearchList[$row->type][] = array(
					"url" => $row->url,
					"name" => $row->name,
					"description" => $row->description,
					"conUrlName" => $row->con_url_name, 
					"couUrlName" => $row->cou_url_name, 
					"cityUrlName" => $row->city_url_name, 
					"insUrlName" => $row->ins_url_name, 
					"couName" => $row->cou_name, 
					"cityName" => $row->city_name, 
					"insName" => $row->ins_name,
				);
			}
		}
		//Common::pr($getSearchList);
		return $getSearchList;
	}

}